﻿using BCCommon;
using Dapper;
using Microsoft.Extensions.Configuration;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BCData
{
    public class SqlCommand : ISqlCommand
    {
        private string connectionString;

        public SqlCommand(string mysqlConnectionString)
        {
            this.connectionString = mysqlConnectionString;
        }

        private void CheckSqlTextOrScriptName(string sqlTextOrScriptName)
        {
            if (string.IsNullOrWhiteSpace(sqlTextOrScriptName))
            {
                throw new ArgumentNullException(nameof(sqlTextOrScriptName));
            }
        }

        public IEnumerable<T> Query<T>(string sqlTextOrScriptName, CommandType commandType, object parameters = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            using (var conn = new MySqlConnection(connectionString))
            {
                return conn.Query<T>(sqlTextOrScriptName, parameters, commandType: commandType);
            }
        }

        public async Task<IEnumerable<T>> QueryAsync<T>(string sqlTextOrScriptName, CommandType commandType, object parameters = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();

                var data = await conn.QueryAsync<T>(sqlTextOrScriptName, parameters, commandType: commandType);

                return data;
            }
        }

        public T QueryEntity<T>(string sqlTextOrScriptName, CommandType commandType, object parameters = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            using (var conn = new MySqlConnection(connectionString))
            {
                return conn.QueryFirstOrDefault<T>(sqlTextOrScriptName, parameters, commandType: commandType);
            }
        }

        public async Task<T> QueryEntityAsync<T>(string sqlTextOrScriptName, CommandType commandType, object parameters = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            using (var conn = new MySqlConnection(connectionString))
            {
                return await conn.QueryFirstOrDefaultAsync<T>(sqlTextOrScriptName, parameters, commandType: commandType);
            }
        }

        public (IEnumerable<T1>, T2) QueryMultiple<T1, T2>(string sqlTextOrScriptName, CommandType commandType, object parameters = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            using (var conn = new MySqlConnection(connectionString))
            {
                SqlMapper.GridReader results = conn.QueryMultiple(sqlTextOrScriptName, parameters, commandType: commandType);

                if (results == null)
                {
                    return (default, default);
                }

                return (results.Read<T1>(), results.ReadFirstOrDefault<T2>());
            }
        }

        public async Task<(IEnumerable<T1>, T2)> QueryMultipleAsync<T1, T2>(string sqlTextOrScriptName, CommandType commandType, object parameters = null)
        {
            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();

                using (var multi = await conn.QueryMultipleAsync(sqlTextOrScriptName, parameters, commandType: commandType))
                {
                    return (multi.Read<T1>(), multi.Read<T2>().FirstOrDefault());
                }
            }
        }
        public async Task<SqlMapper.GridReader> QueryMultipleAsync(string sqlTextOrScriptName, CommandType commandType, object parameters = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();

                return await conn.QueryMultipleAsync(sqlTextOrScriptName, parameters, commandType: commandType);
            }
        }

        public int Execute(string sqlTextOrScriptName, CommandType commandType, object parameters = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            using (var conn = new MySqlConnection(connectionString))
            {
                return conn.Execute(sqlTextOrScriptName, parameters, commandType: commandType);
            }
        }

        public async Task<int> ExecuteAsync(string sqlTextOrScriptName, CommandType commandType, object parameters = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            using (var conn = new MySqlConnection(connectionString))
            {
                return await conn.ExecuteAsync(sqlTextOrScriptName, parameters, commandType: commandType);
            }
        }

        public async Task<int> ExecuteMultipleAsync<T>(string sqlTextOrScriptName, CommandType commandType, List<T> parameters)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            using (var conn = new MySqlConnection(connectionString))
            {
                return await conn.ExecuteAsync(sqlTextOrScriptName, parameters, commandType: commandType);
            }
        }

        public async Task<int> UpdateTransactionExecuteAsync(string sqltextOne, string sqltexTwo, DynamicParameters parametersOne = null, DynamicParameters parametersTwo = null)
        {
            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();

                MySqlTransaction transaction = conn.BeginTransaction();
                try
                {
                    var oneStatus = await conn.ExecuteAsync(sqltextOne, parametersOne, transaction);
                    if (oneStatus > 1)
                    {
                        var twoStatus = await conn.ExecuteAsync(sqltexTwo, parametersTwo, transaction);
                        if (twoStatus > 1)
                        {
                            return twoStatus;
                        }
                    }
                    transaction.Rollback();
                    return 0;
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public async Task<int> TransactionExecuteAsync(List<string> sqls, List<DynamicParameters> parameters)
        {
            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();

                MySqlTransaction transaction = conn.BeginTransaction();
                try
                {
                    for (int i = 0; i < sqls.Count; i++)
                    {
                        await conn.ExecuteAsync(sqls[i], parameters[i], transaction, null, CommandType.Text);

                    }
                    transaction.Commit();
                    return 1;
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public async Task<int> TransactionExecuteMultipleAsync<T>(List<string> sqls, List<DynamicParameters> parameters, string sql2, List<T> parameterList)
        {
            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();
                MySqlTransaction transaction = conn.BeginTransaction();
                try
                {
                    for (int i = 0; i < sqls.Count; i++)
                    {
                        int status = await conn.ExecuteAsync(sqls[i], parameters[i], transaction, null, CommandType.Text);
                        if (status <= 0)
                        {
                            transaction.Rollback();
                            return 0;
                        }
                    }

                    var sql2Status = await conn.ExecuteAsync(sql2, parameterList, transaction, commandType: CommandType.Text);

                    if (sql2Status <= 0)
                    {
                        transaction.Rollback();
                        return 0;
                    }
                    transaction.Commit();
                    return 1;
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public T ExecuteScalar<T>(string sqlTextOrScriptName, CommandType commandType, object parameters = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            using (var conn = new MySqlConnection(connectionString))
            {
                return conn.ExecuteScalar<T>(sqlTextOrScriptName, parameters, commandType: commandType);
            }
        }

        public Task<T> ExecuteScalarAsync<T>(string sqlTextOrScriptName, CommandType commandType, object parameters = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            using (var conn = new MySqlConnection(connectionString))
            {
                return conn.ExecuteScalarAsync<T>(sqlTextOrScriptName, parameters, commandType: commandType);
            }
        }

        public object ExecuteScalar(string sqlTextOrScriptName, CommandType commandType, object parameters = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            using (var conn = new MySqlConnection(connectionString))
            {
                return conn.ExecuteScalar(sqlTextOrScriptName, parameters, commandType: commandType);
            }
        }

        public async Task<object> ExecuteScalarAsync(string sqlTextOrScriptName, CommandType commandType, object parameters = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            using (var conn = new MySqlConnection(connectionString))
            {
                return await conn.ExecuteScalarAsync(sqlTextOrScriptName, parameters, commandType: commandType);
            }
        }

        public object ExecuteReader(string sqlTextOrScriptName, CommandType commandType, object parameters = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            using (var conn = new MySqlConnection(connectionString))
            {
                return conn.ExecuteReader(sqlTextOrScriptName, parameters, commandType: commandType);
            }
        }

        public async Task<object> ExecuteReaderAsync(string sqlTextOrScriptName, CommandType commandType, object parameters = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            using (var conn = new MySqlConnection(connectionString))
            {
                return await conn.ExecuteReaderAsync(sqlTextOrScriptName, parameters, commandType: commandType);
            }
        }

        public string GetInsertSql(string tableName, string[] fieldNames)
        {
            return string.Format("insert into {0} ({1}) Values (?{2});", tableName, string.Join(",", fieldNames), string.Join(",?", fieldNames).Replace("`", ""));
        }

        public string GetUpdateSql(string tableName, string[] modifyFields, string[] conditionFields)
        {
            var modifyFieldStr = string.Join(",", modifyFields.Select(v => string.Format(" {0}=?{0} ", v)).ToArray());
            var conditionFieldStr = string.Join("and", conditionFields.Select(v => string.Format(" {0}=?{0} ", v)).ToArray());
            return string.Format("Update {0} Set {1} Where {2};", tableName, modifyFieldStr, conditionFieldStr);
        }

        public string GetQuerySql(string tableName, string[] fieldNames, string[] conditionFields, string orderByfieldName, bool? ascending = null)
        {
            string sql = string.Format("select {0} from {1} ", string.Join(",", fieldNames), tableName);

            if (conditionFields != null && conditionFields.Any())
            {
                List<string> wheres = new List<string>();
                for (int i = 0; i < conditionFields.Length; i++)
                {
                    wheres.Add(string.Format("{0}=?{0}", conditionFields[i]));
                }
                sql += conditionFields.Length > 0 ? " WHERE " + string.Join(" AND ", wheres) : "";
            }
            if (!string.IsNullOrWhiteSpace(orderByfieldName))
            {
                sql += "Order By " + orderByfieldName;
            }
            if (ascending.HasValue)
            {
                sql += ascending.Value ? " ASC" : " DESC";
            }
            return sql;
        }
    }
}
